package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modellayer.Appointment;
import modellayer.KeyPerson;
import modellayer.ZipCode;

import com.microsoft.sqlserver.jdbc.SQLServerException;

/**
 * The AppointmentDB class to exchange data betwixt SQL database and java
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */

public class AppointmentDB
{
	private Connection con;
	/**
	 * @param con
	 */
	
	/**
	 * Constructor for the AppointmentDB class, also creates a connection to the SQL Database
	 */
	public AppointmentDB()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	
	/**
	 * Insert method to get customer information in to the database
	 * @param customerObj
	 */
	public int insertAppointment(Appointment a) throws Exception
	{
		int rc = -1;
		
		String query = "INSERT INTO sem2_appointment (subject, fromDate, toDate, address, zipcode, addedDate, keyPersonId) VALUES ('"+a.getSubject()+"', '"+a.getFromDate()+"', '"+a.getToDate()+"', '"+a.getAddress()+"', '"+a.getIntZipCode()+"', '"+a.getAddedDate()+"', '"+a.getKeyPerson().getKeyPersonId()+"')";
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException e)
		{
			System.out.println("SQL query: " +query);
			System.out.println("error: " +e);
			
			throw new Exception("Failed to insert Appointment into Database (AppointmentDB)");
		}
		
		return rc;
	}
	
	/**
	 * Method that is used to delete an appointment from the SQL database by using the appointmentNo
	 * @param appointmentNo
	 * @return success
	 */
	public boolean deleteAppointment(int appointmentNo) throws Exception
	{
		boolean success = false;
		
		String query = "DELETE FROM sem2_Appointment WHERE appointmentId='"+appointmentNo+"'";
		
		try 
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeQuery(query);	
			success = true;		
			stmt.close();
		} 
		catch (SQLServerException e) {
			success = true;
		}
		catch (Exception ex) 
		{
			throw new Exception("Appointment deletetion went wrong, try again please! Error: "+ex);
		}
		
		return success;
			
	}
	
	public Appointment findAppointment(String wClause) {
		ArrayList<Appointment> list = miscWhere(wClause);
		if (list.size()!=0) {
			return list.get(0);
		} else {
			return null;
		}
	}
	
	/**
	 * Method that finds all the appointments 
	 * @param wClause
	 * @return miscWhere(wClause)
	 */
	public ArrayList<Appointment> getAppointments(String wClause) {
		return miscWhere(wClause);
	}
	
	/**
	 * miscWhere method that returns an ArrayList with appointment objects
	 * @param wClause
	 * @return array
	 */
	private ArrayList<Appointment> miscWhere(String wClause) {
		ArrayList<Appointment> array = new ArrayList<Appointment>();
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			ResultSet results = stmt.executeQuery(query);
			
			while (results.next()) {
				array.add(buildAppointmentObj(results));
			}
			
		}
		catch(SQLException e) {
			System.out.println("SQL query: " +query);
			System.out.println("error: " +e);
		}
		
		return array;
	}
	
	/**
	 * A method which uses wClause parameters to construct a query, in a format that the SQL database understands
	 * @param wClause
	 * @return query
	 */
	private String buildQuery(String wClause) {
		String query = "SELECT * FROM sem2_Appointment";
		
		if (wClause!=null) {
			if (!wClause.isEmpty()) {
				query += " WHERE "+wClause;
			}
		}
		
		return query;
	}
	
	/**
	 * A method which extracts data from the database into Java
	 * @param results
	 * @return obj
	 */
	private Appointment buildAppointmentObj(ResultSet result) {
		Appointment obj = null;
		
		try {
			ZipCode zipCode = new ZipcodeDB().findZipcode(result.getInt("zipCode"));
			KeyPerson kp = new KeyPersonDB().findKeyPersonById(result.getInt("keyPersonId"));
			
			obj = new Appointment(result.getInt("appointmentId"), result.getString("subject"), result.getLong("fromDate"), result.getLong("toDate"), result.getString("address"), zipCode, result.getLong("addedDate"), kp);
		}
		catch (SQLException e) {
			System.out.println("Failed to build Appointment object in AppointmentDB. Error: "+e);
		}
		
		return obj;
	}
}
